﻿using System;
using System.Text;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

public partial class Admin_Duplicates : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e)
  {
    // For admin pages, check the current user is an admin person
    if (!Conn.IsAdminUser())
    {
      Response.Redirect("Default.aspx");
    }

    if (!IsPostBack)
    {
      ShowDupes();
    }
  }
  void ShowDupes()
  {
    using (SqlConnection conn = Conn.GetOpened())
    {
      string sql = @"select email,count(*) from people where email is not null group by email having count(*)>1 order by email";
      SqlCommand cmd = new SqlCommand(sql, conn);
      SqlDataReader rdr = cmd.ExecuteReader();
      StringBuilder sb = new StringBuilder();
      while (rdr.Read())
        sb.AppendFormat("{0} ({1} duplicates)<br/>", rdr.GetString(0), rdr.GetInt32(1));

      if (sb.Length==0)
        litDupes.Text = "<em>None found</em>";
      else
        litDupes.Text = sb.ToString();
    }
  }

  protected void bnFixDupes_Click(object sender, EventArgs e)
  {
    using (SqlConnection conn = Conn.GetOpened())
    {
      // Make a list of dupes
      List<string> dupeEmails = new List<string>();
      string sql = "select email from people where email is not null group by email having count(*)>1";
      using (SqlCommand cmd = new SqlCommand(sql, conn))
      using (SqlDataReader rdr = cmd.ExecuteReader())
        while (rdr.Read()) dupeEmails.Add(rdr.GetString(0));

      // Work through the list
      foreach (string email in dupeEmails)
        FixDupe(conn, email);
    }
    Response.Redirect("Admin-Duplicates.aspx");
  }

  int InvolvementSortOrder(string involvement)
  {
    using (SqlConnection conn = Conn.GetOpened())
    {
      using (SqlCommand comm = new SqlCommand("select sort_order from involvements where involvement=@involvement", conn))
      {
        comm.Parameters.AddWithValue("@involvement", involvement);
        object o = comm.ExecuteScalar();
        if (o == null || !(o is int)) return int.MaxValue; // Return a high sort order eg this is an unimportant involvement
        return (int)o;
      }
    }
  }

  void FixDupe(SqlConnection conn, string email)
  {
    // TODO in future when there's foreign key tables, update them for the new id

    // Get all the fields, lowest id first so their fields get overwritten by newer ones
    Dictionary<string, object> fields = new Dictionary<string, object>();
    List<int> ids = new List<int>();
    string sql = "select * from people where email=@email order by id";
    using (SqlCommand cmd = new SqlCommand(sql, conn))
    {
      cmd.Parameters.AddWithValue("@email", email);
      using (SqlDataReader rdr = cmd.ExecuteReader())
        while (rdr.Read())
        {
          ids.Add(Convert.ToInt32(rdr["id"]));
          for (int i = 0; i < rdr.FieldCount; i++)
            if (!rdr.IsDBNull(i))
            {
              // OK, we have a not-null value so this is where we decide whether or not to over-write
              // the value from the previous record
              // Normally, we simply overwrite, because since we're sorted in ascending order of id
              // that means the newer record gets precedence
              // However, for the involvement field, the involvement with the lower sort order wins
              string field = rdr.GetName(i); // field name
              if (field.ToLower() == "involvement" && fields.ContainsKey(field))
              { // Apply the special logic for the involvement field because we've got a clash
                int oldPriority = InvolvementSortOrder(fields[field].ToString());
                int newPriority = InvolvementSortOrder(rdr.GetString(i));
                // The new value wins if its sort order is lower
                if (newPriority<oldPriority)
                  fields[field] = rdr.GetValue(i);
              }
              else // Apply the normal logic ie the newer not-null record wins
                fields[field] = rdr.GetValue(i);
            }
        }
    }

    int mainId = ids[0]; // Merge them all to the original (lowest id) record

    // Update all the values on the mainId record
    foreach (string field in fields.Keys)
      if (field != "id")
      {
        string sqlUpd = string.Format("update people set {0}=@value where id={1}", field, mainId);
        using (SqlCommand cmd = new SqlCommand(sqlUpd, conn))
        {
          cmd.Parameters.AddWithValue("@value", fields[field]);
          cmd.ExecuteNonQuery();
        }
      }

    // Delete all other records
    string sqlDel = "delete from people where id=@id";
    foreach (int id in ids)
      if (id != mainId)
        using (SqlCommand cmd = new SqlCommand(sqlDel, conn))
        {
          cmd.Parameters.AddWithValue("@id", id);
          cmd.ExecuteNonQuery();
        }
  }
}
